import Router from "koa-router";
import { pool } from "../../../config/database";
import { authMiddleware } from "../../../middleware/auth";
import { checkAdminPermission, successResponse, handleError } from "../common";
import { RowDataPacket, ResultSetHeader } from "mysql2";

const router = new Router({ prefix: "/api/admin/ai-chat" });

// 获取AI对话记录列表
router.get("/", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const {
      page = 1,
      limit = 20,
      user_id,
      session_id,
      message_type,
      keyword,
      start_date,
      end_date,
      sort = 'created_at_desc'
    } = ctx.query;

    // 参数验证和转换
    const pageNum = Math.max(1, parseInt(page as string) || 1);
    const limitNum = Math.min(Math.max(1, parseInt(limit as string) || 20), 100); // 最大100条，最小1条
    const offset = (pageNum - 1) * limitNum;

    // 构建查询条件
    let whereConditions = [];
    let queryParams = [];

    if (user_id) {
      whereConditions.push("ac.user_id = ?");
      queryParams.push(user_id);
    }

    if (session_id) {
      whereConditions.push("ac.session_id = ?");
      queryParams.push(session_id);
    }

    if (message_type) {
      whereConditions.push("ac.message_type = ?");
      queryParams.push(message_type);
    }

    if (keyword) {
      whereConditions.push("ac.content LIKE ?");
      queryParams.push(`%${keyword}%`);
    }

    if (start_date) {
      whereConditions.push("ac.created_at >= ?");
      queryParams.push(start_date);
    }

    if (end_date) {
      whereConditions.push("ac.created_at <= ?");
      queryParams.push(end_date);
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

    // 构建排序
    let orderClause = 'ORDER BY ac.created_at DESC';
    switch (sort) {
      case 'created_at_asc':
        orderClause = 'ORDER BY ac.created_at ASC';
        break;
      case 'created_at_desc':
        orderClause = 'ORDER BY ac.created_at DESC';
        break;
      case 'updated_at_asc':
        orderClause = 'ORDER BY ac.updated_at ASC';
        break;
      case 'updated_at_desc':
        orderClause = 'ORDER BY ac.updated_at DESC';
        break;
      case 'user_id_asc':
        orderClause = 'ORDER BY ac.user_id ASC, ac.created_at DESC';
        break;
      case 'session_id_asc':
        orderClause = 'ORDER BY ac.session_id ASC, ac.created_at DESC';
        break;
    }

    // 获取总数
    const countQuery = `
      SELECT COUNT(*) as total 
      FROM ai_chat_records ac 
      LEFT JOIN users u ON ac.user_id = u.id 
      ${whereClause}
    `;
    const [countResult] = await pool.execute(countQuery, queryParams) as [RowDataPacket[], any];
    const total = countResult[0].total;

    // 获取数据 - 使用字符串拼接方式避免参数问题
    const dataQuery = `
      SELECT 
        ac.id,
        ac.user_id,
        ac.session_id,
        ac.message_type,
        ac.content,
        ac.created_at,
        ac.updated_at,
        u.username,
        u.email,
        u.nickname
      FROM ai_chat_records ac 
      LEFT JOIN users u ON ac.user_id = u.id 
      ${whereClause} 
      ${orderClause} 
      LIMIT ${limitNum} OFFSET ${offset}
    `;
    
    // 只传递WHERE条件的参数
    const [records] = await pool.execute(dataQuery, queryParams) as [RowDataPacket[], any];

         successResponse(ctx, {
       records,
       pagination: {
         page: pageNum,
         limit: limitNum,
         total,
         totalPages: Math.ceil(total / limitNum)
       }
     }, "获取AI对话记录成功");

  } catch (error) {
    handleError(ctx, error, "获取AI对话记录失败");
  }
});

// 获取AI对话记录详情
router.get("/:id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { id } = ctx.params;

    const [records] = await pool.execute(`
      SELECT 
        ac.id,
        ac.user_id,
        ac.session_id,
        ac.message_type,
        ac.content,
        ac.created_at,
        ac.updated_at,
        u.username,
        u.email,
        u.nickname
      FROM ai_chat_records ac 
      LEFT JOIN users u ON ac.user_id = u.id 
      WHERE ac.id = ?
    `, [id]) as [RowDataPacket[], any];

    if (records.length === 0) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: "AI对话记录不存在"
      };
      return;
    }

    successResponse(ctx, records[0], "获取AI对话记录详情成功");

  } catch (error) {
    handleError(ctx, error, "获取AI对话记录详情失败");
  }
});

// 获取某个会话的所有对话记录
router.get("/session/:session_id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { session_id } = ctx.params;
    const { sort = 'created_at_asc' } = ctx.query;

    let orderClause = 'ORDER BY ac.created_at ASC';
    if (sort === 'created_at_desc') {
      orderClause = 'ORDER BY ac.created_at DESC';
    }

    const [records] = await pool.execute(`
      SELECT 
        ac.id,
        ac.user_id,
        ac.session_id,
        ac.message_type,
        ac.content,
        ac.created_at,
        ac.updated_at,
        u.username,
        u.email,
        u.nickname
      FROM ai_chat_records ac 
      LEFT JOIN users u ON ac.user_id = u.id 
      WHERE ac.session_id = ? 
      ${orderClause}
    `, [session_id]) as [RowDataPacket[], any];

    successResponse(ctx, records, "获取会话对话记录成功");

  } catch (error) {
    handleError(ctx, error, "获取会话对话记录失败");
  }
});

// 删除单条AI对话记录
router.delete("/:id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { id } = ctx.params;

    const [result] = await pool.execute(
      "DELETE FROM ai_chat_records WHERE id = ?",
      [id]
    ) as [ResultSetHeader, any];

    if (result.affectedRows === 0) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: "AI对话记录不存在"
      };
      return;
    }

    successResponse(ctx, null, "删除AI对话记录成功");

  } catch (error) {
    handleError(ctx, error, "删除AI对话记录失败");
  }
});

// 批量删除AI对话记录
router.delete("/batch", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { ids } = ctx.request.body as { ids: number[] };

    if (!ids || !Array.isArray(ids) || ids.length === 0) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "请提供要删除的记录ID数组"
      };
      return;
    }

    const placeholders = ids.map(() => '?').join(',');
    const [result] = await pool.execute(
      `DELETE FROM ai_chat_records WHERE id IN (${placeholders})`,
      ids
    ) as [ResultSetHeader, any];

    successResponse(ctx, {
      deletedCount: result.affectedRows
    }, `成功删除 ${result.affectedRows} 条AI对话记录`);

  } catch (error) {
    handleError(ctx, error, "批量删除AI对话记录失败");
  }
});

// 删除某个会话的所有对话记录
router.delete("/session/:session_id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { session_id } = ctx.params;

    const [result] = await pool.execute(
      "DELETE FROM ai_chat_records WHERE session_id = ?",
      [session_id]
    ) as [ResultSetHeader, any];

    successResponse(ctx, {
      deletedCount: result.affectedRows
    }, `成功删除会话 ${session_id} 的 ${result.affectedRows} 条对话记录`);

  } catch (error) {
    handleError(ctx, error, "删除会话对话记录失败");
  }
});

// 删除某个用户的所有AI对话记录
router.delete("/user/:user_id", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { user_id } = ctx.params;

    const [result] = await pool.execute(
      "DELETE FROM ai_chat_records WHERE user_id = ?",
      [user_id]
    ) as [ResultSetHeader, any];

    successResponse(ctx, {
      deletedCount: result.affectedRows
    }, `成功删除用户 ${user_id} 的 ${result.affectedRows} 条AI对话记录`);

  } catch (error) {
    handleError(ctx, error, "删除用户AI对话记录失败");
  }
});

// 清理过期的AI对话记录（可根据需要调整天数）
router.delete("/cleanup/expired", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { days = 30 } = ctx.query; // 默认清理30天前的记录

    const [result] = await pool.execute(
      "DELETE FROM ai_chat_records WHERE created_at < DATE_SUB(NOW(), INTERVAL ? DAY)",
      [days]
    ) as [ResultSetHeader, any];

    successResponse(ctx, {
      deletedCount: result.affectedRows,
      cleanupDays: Number(days)
    }, `成功清理 ${days} 天前的 ${result.affectedRows} 条AI对话记录`);

  } catch (error) {
    handleError(ctx, error, "清理过期AI对话记录失败");
  }
});

// 获取AI对话统计信息
router.get("/stats/overview", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    // 获取总记录数
    const [totalResult] = await pool.execute(
      "SELECT COUNT(*) as total FROM ai_chat_records"
    ) as [RowDataPacket[], any];

    // 获取今日记录数
    const [todayResult] = await pool.execute(
      "SELECT COUNT(*) as today FROM ai_chat_records WHERE DATE(created_at) = CURDATE()"
    ) as [RowDataPacket[], any];

    // 获取本周记录数
    const [weekResult] = await pool.execute(
      "SELECT COUNT(*) as week FROM ai_chat_records WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)"
    ) as [RowDataPacket[], any];

    // 获取活跃用户数
    const [activeUsersResult] = await pool.execute(
      "SELECT COUNT(DISTINCT user_id) as active_users FROM ai_chat_records WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)"
    ) as [RowDataPacket[], any];

    // 获取会话数
    const [sessionsResult] = await pool.execute(
      "SELECT COUNT(DISTINCT session_id) as sessions FROM ai_chat_records"
    ) as [RowDataPacket[], any];

    // 按消息类型统计
    const [typeStatsResult] = await pool.execute(
      "SELECT message_type, COUNT(*) as count FROM ai_chat_records GROUP BY message_type"
    ) as [RowDataPacket[], any];

    successResponse(ctx, {
      total: totalResult[0].total,
      today: todayResult[0].today,
      thisWeek: weekResult[0].week,
      activeUsers: activeUsersResult[0].active_users,
      totalSessions: sessionsResult[0].sessions,
      messageTypeStats: typeStatsResult
    }, "获取AI对话统计信息成功");

  } catch (error) {
    handleError(ctx, error, "获取AI对话统计信息失败");
  }
});

export default router;
